package com.cm367.teamc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;

import com.cm367.teamc.bean.Rule;
import com.cm367.teamc.util.DateUtil;

@ManagedBean
@ApplicationScoped
public class LeaveRulesDAO {

	@ManagedProperty(value = "#{publicHolidayDAO}")
	private PublicHolidayDAO publicHolidayDAO;

	public PublicHolidayDAO getPublicHolidayDAO() {
		return publicHolidayDAO;
	}

	public void setPublicHolidayDAO(PublicHolidayDAO publicHolidayDAO) {
		this.publicHolidayDAO = publicHolidayDAO;
	}

	public boolean addTypeDays(String type, int days) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("insert into rules values(?,?)");
		ps.setString(1, type);
		ps.setInt(2, days);
		ps.execute();
		ps.close();
		return true;
	}

	public boolean existType(String type) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select count(*) from rules where leavetype=?");
		ps.setString(1, type);
		ResultSet rs = ps.executeQuery();
		rs.next();
		int count = rs.getInt(1);
		rs.close();
		ps.close();
		if (count == 0) {
			return false;
		}
		return true;
	}

	public boolean updateType(String type, int day) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("update rules set days=? where leavetype=?");
		ps.setInt(1, day);
		ps.setString(2, type);
		ps.execute();
		ps.close();
		return true;
	}

	public boolean deleteTypeDays(String type) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("delete from rules where leavetype=?");
		ps.setString(1, type);
		ps.execute();
		ps.close();
		return true;
	}

	public int getPending(String userid, String type, int year)
			throws SQLException {
		int day = 0;
		List<String> holidays = publicHolidayDAO.findHolidayDates(year);
		// String currentDate = DateUtil.getStringDate(Calendar.getInstance()
		// .getTime());
		String from = year + "-01-01";
		String to = year + "-12-31";
		String sql = "select * from leave where userid=? and type=? and status=? and datefrom<=? and dateto>=?";
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, userid);
		ps.setString(2, type);
		ps.setString(3, "Pending");
		ps.setString(4, to);
		ps.setString(5, from);
		// ps.setString(6, currentDate);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			String dateFrom = rs.getString("datefrom");
			String dateTo = rs.getString("dateto");
			day += DateUtil.countDays(year, dateFrom, dateTo, holidays);
		}
		rs.close();
		ps.close();
		return day;
	}

	public int getApproved(String userid, String type, int year)
			throws SQLException {
		int day = 0;
		List<String> holidays = publicHolidayDAO.findHolidayDates(year);
		String from = year + "-01-01";
		String to = year + "-12-31";
		String sql = "select * from leave where userid=? and type=? and (status=? or status=? or status=?) and datefrom<=? and dateto>=?";
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, userid);
		ps.setString(2, type);
		ps.setString(3, "Approved");
		ps.setString(4, "Cancel Leave");
		ps.setString(5, "Rejected Cancel Leave");
		ps.setString(6, to);
		ps.setString(7, from);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			String dateFrom = rs.getString("datefrom");
			String dateTo = rs.getString("dateto");
			day += DateUtil.countDays(year, dateFrom, dateTo, holidays);
		}
		rs.close();
		ps.close();
		return day;
	}

	public int getLeft(String userid, String type, int year)
			throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select * from rules where leavetype=?");
		ps.setString(1, type);
		ResultSet rs = ps.executeQuery();
		int days = 0;
		if (rs.next()) {
			days = rs.getInt("days");
		}
		rs.close();
		ps.close();
		int day = 0;
		day += this.getPending(userid, type, year);
		day += this.getApproved(userid, type, year);
		return days - day;
	}

	public List<String> getTypes() throws SQLException {
		List<String> types = new ArrayList<String>();
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select * from rules order by leavetype");
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			types.add(rs.getString("leavetype"));
		}
		rs.close();
		ps.close();
		return types;
	}

	public List<Rule> findAllType() throws SQLException {
		List<Rule> rules = new ArrayList<Rule>();
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select * from rules order by leavetype");
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			String type = rs.getString("leavetype");
			int day = rs.getInt("days");
			rules.add(new Rule(type, day));
		}
		rs.close();
		ps.close();
		return rules;
	}

}
